using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Entities;
namespace DAL
{
    public class tbSanPhamDAL
    {
        private SqlConnection cn = null;
        public tbSanPhamDAL()
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
            cn.Open();
        }
        public int Insert(tbSanPham sanpham)
        {
            SqlCommand cm = new SqlCommand("sp_SanPham_Insert", cn);
            cm.CommandType =System .Data .CommandType .StoredProcedure ;
            cm.Parameters .AddWithValue ("@TenSP",sanpham .TenSP );
            cm.Parameters .AddWithValue ("@IdLoaiSP",sanpham .IdLoaiSP  );
            cm.Parameters .AddWithValue ("@IdDiaDiem",sanpham .IdDiaDiem  );
            cm.Parameters .AddWithValue ("@IdTrangThaiSP",sanpham .IdTrangThaiSP  );
            cm.Parameters .AddWithValue ("@IdNhanVien",sanpham .IdNhanVien  );
            cm.Parameters .AddWithValue ("@IdDoanhNghiep",sanpham .IdDoanhNghiep  );
            cm.Parameters .AddWithValue ("@GiaBan",sanpham .GiaBan  );
            cm.Parameters .AddWithValue ("@GiaTriThuc",sanpham .GiaTriThuc  );
            cm.Parameters .AddWithValue ("@DKSuDung",sanpham .DKSungDung );
            cm.Parameters .AddWithValue ("@MoTaSP",sanpham .MoTa );
            cm.Parameters .AddWithValue ("@SLBan",sanpham .SLBan  );
            cm.Parameters .AddWithValue ("@SLDaBan",sanpham .SLDaBan  );
            cm.Parameters .AddWithValue ("@NgayBDBan",sanpham .NgayBDBan  );
            cm.Parameters .AddWithValue ("@NgayKTBan",sanpham .NgayKTBan  );
            cm.Parameters .AddWithValue ("@NgayUpdate",sanpham .NgayUpdate  );
            cm.Parameters .AddWithValue ("@NgayDNBan",sanpham .NgayDNBan  );
            cm.Parameters .AddWithValue ("@TLHoaHong",sanpham .TLHoaHong  );
            return int.Parse (cm.ExecuteScalar ().ToString ());

        }
        public void Update(tbSanPham sanpham)
        {
            SqlCommand cm = new SqlCommand("sp_SanPham_Update", cn);
            cm.CommandType = System.Data.CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@IdSanPham", sanpham.IdSanPham );
            cm.Parameters.AddWithValue("@TenSP", sanpham.TenSP);
            cm.Parameters.AddWithValue("@IdLoaiSP", sanpham.IdLoaiSP);
            cm.Parameters.AddWithValue("@IdDiaDiem", sanpham.IdDiaDiem);
            cm.Parameters.AddWithValue("@IdTrangThaiSP", sanpham.IdTrangThaiSP);
            cm.Parameters.AddWithValue("@IdNhanVien", sanpham.IdNhanVien);
            cm.Parameters.AddWithValue("@IdDoanhNghiep", sanpham.IdDoanhNghiep);
            cm.Parameters.AddWithValue("@GiaBan", sanpham.GiaBan);
            cm.Parameters.AddWithValue("@GiaTriThuc", sanpham.GiaTriThuc);
            cm.Parameters.AddWithValue("@DKSuDung", sanpham.DKSungDung);
            cm.Parameters.AddWithValue("@MoTaSP", sanpham.MoTa);
            cm.Parameters.AddWithValue("@SLBan", sanpham.SLBan);
            cm.Parameters.AddWithValue("@SLDaBan", sanpham.SLDaBan);
            cm.Parameters.AddWithValue("@NgayBDBan", sanpham.NgayBDBan);
            cm.Parameters.AddWithValue("@NgayKTBan", sanpham.NgayKTBan);
            cm.Parameters.AddWithValue("@NgayUpdate", sanpham.NgayUpdate);
            cm.Parameters.AddWithValue("@NgayDNBan", sanpham.NgayDNBan);
            cm.Parameters.AddWithValue("@TLHoaHong", sanpham.TLHoaHong);
            cm.ExecuteNonQuery();
        }
        public void Delete(int Id)
        {
            SqlCommand cm = new SqlCommand("sp_SanPham_Delete", cn);
            cm.CommandType = System.Data.CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@IdSanPham", Id);
            cm.ExecuteNonQuery();
        }
        public tbSanPhamCollection  Get(string where, int PageIndex, int PageSize, string OrderBy, string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand("sp_SanPham_Get", cn);
            cm.CommandType = System.Data.CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@Where", where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);
            cm.Parameters.Add(new SqlParameter("@TotalRecords", System.Data.DbType.Int32)).Direction = System.Data.ParameterDirection.Output;
            SqlDataReader dr = cm.ExecuteReader();
            tbSanPhamCollection SPColection = new tbSanPhamCollection();
            while (dr.Read())
            {
                tbSanPham sanpham = new tbSanPham();
                sanpham .IdSanPham  = int.Parse(dr.GetValue(0).ToString());
                sanpham.TenSP  = dr.GetValue(1).ToString();
                sanpham.IdLoaiSP  = int.Parse (dr.GetValue(2).ToString());
                sanpham.IdDiaDiem  = int.Parse (dr.GetValue(3).ToString());
                sanpham.IdTrangThaiSP  = int.Parse (dr.GetValue(4).ToString());
                sanpham.IdNhanVien  = int.Parse (dr.GetValue(5).ToString());
                sanpham.IdDoanhNghiep  = int.Parse (dr.GetValue(6).ToString());
                sanpham.GiaBan  = int.Parse (dr.GetValue(7).ToString());
                sanpham.GiaTriThuc  = int.Parse (dr.GetValue(8).ToString());
                sanpham.DKSungDung  = dr.GetValue(9).ToString();
                sanpham.MoTa  = dr.GetValue(10).ToString();
                sanpham.SLBan  = int.Parse (dr.GetValue(11).ToString());
                sanpham.SLDaBan  = int.Parse (dr.GetValue(12).ToString());
                sanpham.NgayBDBan  = DateTime .Parse (dr.GetValue(13).ToString());
                sanpham.NgayKTBan  = DateTime .Parse (dr.GetValue(14).ToString());
                sanpham.NgayUpdate  = DateTime .Parse (dr.GetValue(15).ToString());
                sanpham.NgayDNBan  = DateTime .Parse (dr.GetValue(16).ToString());
                sanpham.TLHoaHong  = float .Parse (dr.GetValue(17).ToString());
                SPColection.Add(sanpham);
            }
            dr.Close();
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
            return SPColection;
        }
    }
}
